BatchCollisionCount Property and Update Method Example

This example uses the BatchCollisionCount property and the Update method to demonstrate batch updating where any collisions are resolved by forcing the batch update.

Sub BatchX()

   Dim wrkMain As Workspace
   Dim conMain As Connection
   Dim rstTemp As Recordset
   Dim intLoop As Integer
   Dim strPrompt As String

   Set wrkMain = CreateWorkspace("ODBCWorkspace", _
      "admin", "", dbUseODBC)
   ' This DefaultCursorDriver setting is required for
   ' batch updating.
   wrkMain.DefaultCursorDriver = dbUseClientBatchCursor

   ' Note: The DSN referenced below must be configured to 
   '       use Microsoft Windows NT Authentication Mode to 
   '       authorize user access to the Microsoft SQL Server.
   Set conMain = wrkMain.OpenConnection("Publishers", _
      dbDriverNoPrompt, False, _
      "ODBC;DATABASE=pubs;DSN=Publishers")
      
   ' The following locking argument is required for
   ' batch updating. It is also required that a table
   ' with a primary key is used.
   Set rstTemp = conMain.OpenRecordset( _
      "SELECT * FROM roysched", dbOpenDynaset, 0, _
      dbOptimisticBatch)

   With rstTemp
      ' Modify data in local recordset.
      Do While Not .EOF
         .Edit
         If !royalty <= 20 Then
            !royalty = !royalty - 4
         Else
            !royalty = !royalty + 2
         End If
         .Update
         .MoveNext
      Loop

      ' Attempt a batch update.
      .Update dbUpdateBatch

      ' If there are collisions, give the user the option
      ' of forcing the changes or resolving them
      ' individually.
      If .BatchCollisionCount > 0 Then
         strPrompt = "There are collisions. " & vbCr & _
            "Do you want the program to force " & _
            vbCr & "an update using the local data?"
         If MsgBox(strPrompt, vbYesNo) = vbYes Then _
            .Update dbUpdateBatch, True
      End If

      .Close
   End With

   conMain.Close
   wrkMain.Close

End Sub